﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using DevExpress.XtraEditors;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_CapnhatthucphamDAL : ClsConnection
    {


        public DataTable GetTable(string ProName, string Value, string Name)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(ProName, cn);
            DataTable table = new DataTable();

            try
            {
                commad.Parameters.Add(Value, OleDbType.VarWChar).Value = Name;
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }


        public void InsertTPHH(string proName, TextEdit[] Nhom, int Id)
        {

            string query;
            if (proName.ToUpper() == "PRCTBLDM_THANHPHANHOAHOC_INSERT")
                query = QueryString.PRCTBLDM_THANHPHANHOAHOC_INSERT;
            else
                query = QueryString.PRCTBLDM_THANHPHANHOAHOC_UPDATE;
            OleDbConnection cnn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(query, cnn);
            try
            {
                comand.Parameters.Add("@IdThucpham_thucan", OleDbType.Integer).Value = Id;
                comand.Parameters.Add("@Tphh_Nuoc", OleDbType.Double).Value = Nhom[0].Text;
                comand.Parameters.Add("@Tphh_Protid", OleDbType.Double).Value = Nhom[1].Text;
                comand.Parameters.Add("@Tphh_Lipid", OleDbType.Double).Value = Nhom[2].Text;
                comand.Parameters.Add("@Tphh_GlucId", OleDbType.Double).Value = Nhom[3].Text;
                comand.Parameters.Add("@Tphh_Cellulose", OleDbType.Double).Value = Nhom[4].Text;
                comand.Parameters.Add("@Tphh_Tro", OleDbType.Double).Value = Nhom[5].Text;
                comand.Parameters.Add("@Tpmk_Calci", OleDbType.Double).Value = Nhom[6].Text;
                comand.Parameters.Add("@Tpmk_Photpho", OleDbType.Double).Value = Nhom[7].Text;
                comand.Parameters.Add("@Tpmk_Fe", OleDbType.Double).Value = Nhom[8].Text;
                comand.Parameters.Add("@Tpvitamin_Caroten", OleDbType.Double).Value = Nhom[9].Text;
                comand.Parameters.Add("@Tpvitamin_VitaminA", OleDbType.Double).Value = Nhom[10].Text;
                comand.Parameters.Add("@Tpvitamin_VitaminB1", OleDbType.Double).Value = 0;
                comand.Parameters.Add("@Tpvitamin_VitaminB2", OleDbType.Double).Value = 0;
                comand.Parameters.Add("@Tpvitamin_PP", OleDbType.Double).Value = Nhom[13].Text;
                comand.Parameters.Add("@Tpvitamin_VitaminC", OleDbType.Double).Value = Nhom[14].Text;
                comand.Parameters.Add("@Tpmk_Natri", OleDbType.Double).Value = Nhom[15].Text;
                comand.Parameters.Add("@Tpmk_Axitfomic", OleDbType.Double).Value = Nhom[17].Text;
                comand.Parameters.Add("@Tpmk_Kali", OleDbType.Double).Value = Nhom[16].Text;
                comand.ExecuteNonQuery();
                cnn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }

        }

        //
        // Update thuc pham
        //

        public void InsertData(int id_ThucPham, string ProName, int[] Nhom, string tenThucPham, float calo, bool[] nguyenLieu, DateTime nam)
        {
            OleDbConnection cnn = OleDBConnect();
            QueryString.PRCTBLDM_CAPNHATTHUCPHAM_UPDATE = "UPDATE TblThucpham_thucan SET NhomthucanId=" + Nhom[0] + ", " +
                "Tenthucan='" + tenThucPham + "', Tylequydoi=" + Nhom[1] + ", Tylethai=" + Nhom[2] +
                ", DonvitinhId=" + Nhom[3] + ", Calo=" + calo + ", Nguyenlieu=" + (nguyenLieu[0] ? "-1" : "0") + ", " +
                "Nguondongvat=" + (nguyenLieu[1] ? "-1" : "0") + ", Nam='" + nam + "' WHERE Id = " + id_ThucPham;
            OleDbCommand comand = new OleDbCommand(QueryString.PRCTBLDM_CAPNHATTHUCPHAM_UPDATE, cnn);
            try
            {
                comand.ExecuteNonQuery();
                cnn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error Insert " + ex.Message); }
        }

        public void InsertData(int[] Nhom, string tenThucPham, float calo, bool[] nguyenLieu, DateTime nam)
        {
                string query = QueryString.PRCTBLDM_CAPNHATTHUCPHAM_INSERT;
                OleDbConnection cnn = ClsConnection.OleDBConnect();
                OleDbCommand comand = new OleDbCommand(query, cnn);
                try
                {
                    comand.Parameters.Add("@NhomthucanId", OleDbType.Integer).Value = Nhom[0];
                    comand.Parameters.Add("@Tenthucan", OleDbType.VarWChar).Value = tenThucPham;
                    comand.Parameters.Add("@Tylequydoi", OleDbType.Integer).Value = Nhom[1];
                    comand.Parameters.Add("@Tylethai", OleDbType.Integer).Value = Nhom[2];
                    comand.Parameters.Add("@DonvitinhId", OleDbType.Integer).Value = Nhom[3];
                    comand.Parameters.Add("@Calo", OleDbType.Integer).Value = calo;
                    comand.Parameters.Add("@Nguyenlieu", OleDbType.Integer).Value = nguyenLieu[0];
                    comand.Parameters.Add("@Nguondongvat", OleDbType.Integer).Value = nguyenLieu[1];
                    comand.Parameters.Add("@Nam", OleDbType.Date).Value = nam;
                    comand.ExecuteNonQuery();
                    cnn.Close();
                }
                catch (System.Exception ex)
                { MessageBox.Show("Error Insert " + ex.Message); }
        }


        public DataTable GetbyName(string Value, string Name)
        {
            string query = QueryString.PRCTBLDM_CAPNHATTHUCPHAM_FINDSAMENAME;
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(query, cn);
            DataTable table = new DataTable();

            try
            {
                commad.Parameters.Add(Value, OleDbType.VarWChar).Value = Name;
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }
    }
}
